﻿using CNKI.TPI.Web.Admin.IDAO;
using CNKI.TPI.Web.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace CNKI.TPI.Web.Admin.DAO
{
    public class UserCenterDAO : BaseDAO, IUserCenterDAO
    {

        public DataTable GetSearchPage(IList<int> dbid, DateTime? startDate, DateTime? endDate, int startIndex, int pagecount, out int count)
        {
            DataTable dt = null;
            string sql = @"select s.DbID,'' as DbName,s.Str_Query,s.OperateTime CreateTime, '' as SearchUrL, s.searchlogid, s.searchType, s.TopicCLS, s.TopicNode from tpi.searchlog s where (s.Str_Query!='') 
                           and ((locate('SYS_FLD_CHECKSTATE',s.Str_Query)>0 and length(s.Str_Query)>35) or locate('SYS_FLD_CHECKSTATE',s.Str_Query)<=0) {0} order by s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join("', s.DbID) or FIND_IN_SET('", dbid);
                where = " and (FIND_IN_SET('" + strid + "', s.DbID))";
            }
            string uid = SessionHelper.GetUserInfo().UserID;
            where += " and s.UserID=" + uid;
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            dt = Context.ExecuteSql(string.Format(sql, where), startIndex, pagecount, out count, false, "mysql");

            return dt;

        }

        public DataTable GetSearchPage(IList<int> dbid, DateTime? startDate, DateTime? endDate, string userid, string startIP, string endIP)
        {
            DataTable dt = null;
            string sql = @"select s.UserName, s.IP, s.DbID,'' as DbName,s.Str_Query,s.OperateTime CreateTime, '' as SearchUrL, s.searchlogid, s.searchType, s.TopicCLS, s.TopicNode from tpi.searchlog s where (s.Str_Query!='') 
                           and ((locate('SYS_FLD_CHECKSTATE',s.Str_Query)>0 and length(s.Str_Query)>35) or locate('SYS_FLD_CHECKSTATE',s.Str_Query)<=0) {0} order by s.UserName, s.IP, s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join(",", dbid);
                where = " and s.DbID in(" + strid + ")";
            }
            //用户名
            if (!string.IsNullOrEmpty(userid))
            {
                where += " and s.userid='" + userid + "'";
            }
            //ip段
            if (!string.IsNullOrEmpty(startIP) && !string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "') and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            else if (!string.IsNullOrEmpty(startIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "')";
            }
            else if (!string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            //检索时间
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            dt = Context.ExecuteSql(string.Format(sql, where), "mysql");

            return dt;

        }
        public DataTable GetViewPage(IList<int> dbid, string title, DateTime? startDate, DateTime? endDate, int startIndex, int pagecount, out int count)
        {
            DataTable dt = null;
            string sql = "select s.DbID,'' as DbName,'' as DetailUrL,s.Title,s.OperateTime CreateTime,s.RecordID as SYS_FLD_SYSID,s.DbCode from tpi.browselog s where s.Title!='' {0} order by s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join(",", dbid);
                where = " and s.DbID in(" + strid + ")";
            }
            if (!string.IsNullOrEmpty(title))
            {
                where += " and s.Title like '%" + title + "%'";
            }
            string uid = SessionHelper.GetUserInfo().UserID;
            where += " and s.UserID=" + uid;
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            //if (where != "")
            //{
            //    where = where.Substring(4, where.Length - 4);
            //}
            dt = Context.ExecuteSql(string.Format(sql, where), startIndex, pagecount, out count, false, "mysql");

            return dt;

        }

        public DataTable GetViewPage(IList<int> dbid, string title, DateTime? startDate, DateTime? endDate, string userid, string startIP, string endIP)
        {
            DataTable dt = null;
            string sql = "select s.UserName, s.IP, s.DbID,'' as DbName,'' as DetailUrL,s.Title,s.OperateTime CreateTime,s.RecordID as SYS_FLD_SYSID,s.DbCode from tpi.browselog s where s.Title!='' {0} order by s.UserName, s.IP, s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join(",", dbid);
                where = " and s.DbID in(" + strid + ")";
            }
            if (!string.IsNullOrEmpty(title))
            {
                where += " and s.Title like '%" + title + "%'";
            }
            //用户名
            if (!string.IsNullOrEmpty(userid))
            {
                where += " and s.userid='" + userid + "'";
            }
            //ip段
            if (!string.IsNullOrEmpty(startIP) && !string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "') and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            else if (!string.IsNullOrEmpty(startIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "')";
            }
            else if (!string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            //if (where != "")
            //{
            //    where = where.Substring(4, where.Length - 4);
            //}
            dt = Context.ExecuteSql(string.Format(sql, where), "mysql");

            return dt;

        }
        public DataTable GetDownPage(IList<int> dbid, string title, DateTime? startDate, DateTime? endDate, int startIndex, int pagecount, out int count)
        {
            DataTable dt = null;
            string sql = "select s.DbID,'' as DbName,'' as DetailUrL,s.Title,s.OperateTime CreateTime,s.RecordID as SYS_FLD_SYSID,s.DbCode from tpi.downloadlog s where s.Title!='' {0} order by s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join(",", dbid);
                where = " and s.DbID in(" + strid + ")";
            }
            if (!string.IsNullOrEmpty(title))
            {
                where += " and s.Title like '%" + title + "%'";
            }
            string uid = SessionHelper.GetUserInfo().UserID;
            where += " and s.UserID=" + uid;
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            //if (where != "")
            //{
            //    where = where.Substring(4, where.Length - 4);
            //}
            dt = Context.ExecuteSql(string.Format(sql, where), startIndex, pagecount, out count, false, "mysql");

            return dt;

        }

        public DataTable GetDownPage(IList<int> dbid, string title, DateTime? startDate, DateTime? endDate, string userid, string startIP, string endIP)
        {
            DataTable dt = null;
            string sql = "select s.UserName, s.IP, s.DbID,'' as DbName,'' as DetailUrL,s.Title,s.OperateTime CreateTime,s.RecordID as SYS_FLD_SYSID,s.DbCode from tpi.downloadlog s where s.Title!='' {0} order by s.UserName, s.IP, s.OperateTime desc";
            string where = "";
            if (dbid != null && dbid.Count > 0)
            {
                string strid = String.Join(",", dbid);
                where = " and s.DbID in(" + strid + ")";
            }
            if (!string.IsNullOrEmpty(title))
            {
                where += " and s.Title like '%" + title + "%'";
            }
            //用户名
            if (!string.IsNullOrEmpty(userid))
            {
                where += " and s.userid='" + userid + "'";
            }
            //ip段
            if (!string.IsNullOrEmpty(startIP) && !string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "') and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            else if (!string.IsNullOrEmpty(startIP))
            {
                where += " and inet_aton(s.IP) >= inet_aton('" + startIP + "')";
            }
            else if (!string.IsNullOrEmpty(endIP))
            {
                where += " and inet_aton(s.IP) <= inet_aton('" + endIP + "')";
            }
            if (startDate != null && endDate != null)
            {
                where += " and s.OperateTime >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.OperateTime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                where += " and s.OperateTime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                where += " and s.OperateTime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            //if (where != "")
            //{
            //    where = where.Substring(4, where.Length - 4);
            //}
            dt = Context.ExecuteSql(string.Format(sql, where), "mysql");

            return dt;

        }

        /// <summary>
        /// 获取收藏分类类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetFavoriteCategory(string userCode, int startIndex, int pagecount, out int count)
        {
            DataTable dt = null;
            string sql = "SELECT t.FavoriteCategoryID,t.UserCode,t.CategoryName,t.CategoryStatus,t.SortNo FROM tpi.favoritecategory t  where t.UserCode = '{0}' and t.CategoryStatus=1 order by t.SortNo";
            dt = Context.ExecuteSql(string.Format(sql, userCode), startIndex, pagecount, out count, false, "mysql");
            return dt;

        }

        /// <summary>
        /// 获取收藏分类类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetFavoriteCategory(string userCode)
        {
            DataTable dt = null;
            string sql = "SELECT t.FavoriteCategoryID,t.UserCode,t.CategoryName,t.CategoryStatus,t.SortNo FROM tpi.favoritecategory t  where t.UserCode = '{0}' and t.CategoryStatus=1 order by t.SortNo";
            dt = Context.ExecuteSql(string.Format(sql, userCode), "mysql");
            return dt;

        }
        /// <summary>
        /// 新增分类数据
        /// </summary>
        /// <param name="catename"></param>
        /// <param name="catestatus"></param>
        /// <param name="sortno"></param>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public bool InsertFavoriteCategory(string catename, int catestatus, int sortno, string usercode)
        {
            string sql = "insert into tpi.favoritecategory (UserCode,CategoryName,CategoryStatus,SortNo) values('{0}','{1}',{2},{3})";
            return Context.ExecuteNonQuery(string.Format(sql, usercode, catename, catestatus, sortno), "mysql");
        }
        /// <summary>
        /// 更新分类名称
        /// </summary>
        /// <param name="catename"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateFavoriteCategoryName(string catename, int id)
        {
            string sql = "update tpi.favoritecategory set CategoryName='" + catename + "' where FavoriteCategoryID=" + id;
            return Context.ExecuteNonQuery(sql, "mysql");
        }
        /// <summary>
        /// 更新分类顺序
        /// </summary>
        /// <param name="sortno"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateFavoriteCategorySort(int sortno, int id)
        {
            string sql = "update tpi.favoritecategory set SortNo='" + sortno + "' where FavoriteCategoryID=" + id;
            return Context.ExecuteNonQuery(sql, "mysql");
        }
        /// <summary>
        /// 删除分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelFavoriteCategory(int id)
        {
            bool m = false;
            string sql = "update tpi.favoritecategory set CategoryStatus=9 where FavoriteCategoryID=" + id;
            string sql2= "update tpi.favoritefolder  set FavoriteStatus=9 where FavoriteCategoryID=" + id;
            m=Context.ExecuteNonQuery(sql, "mysql");
            if (m)
            {
                m=Context.ExecuteNonQuery(sql2, "mysql");
            }
            return m;
        }
        /// <summary>
        /// 获取最大的序号
        /// </summary>
        /// <returns></returns>
        public int GetMaxSort()
        {
            int count = 0;
            string sql = " SELECT max(t.SortNo) as sortno FROM tpi.favoritecategory t ";
            DataTable dt = Context.ExecuteSql(sql, "mysql");
            if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
            {
                count = int.Parse(dt.Rows[0][0].ToString());
            }
            return count;
        }
        /// <summary>
        /// 判断是否存在分类名称 ，如果已经存在返回该记录ID，不存在返回0
        /// </summary>
        /// <returns></returns>
        public int IsExistFavName(string userCode, string name)
        {
            int id = 0;
            string sql = " select t.FavoriteCategoryID from tpi.favoritecategory t where t.categorystatus = 1 and t.UserCode = '{0}' and t.CategoryName='{1}'";
            DataTable dt = Context.ExecuteSql(string.Format(sql, userCode, name), "mysql");
            if (dt != null && dt.Rows.Count > 0)
            {
                id = int.Parse(dt.Rows[0][0].ToString());
              
            }
            return id;
        }
        /// <summary>
        /// 新增收藏记录
        /// </summary>
        /// <param name="catename"></param>
        /// <param name="catestatus"></param>
        /// <param name="sortno"></param>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public bool InsertFavoriteFolder(int favCateID, string dbCode,string dbName, string recid, string recTitle)
        {
            string usercode = SessionHelper.GetUserInfo().UserCode;
            string sql = @"insert into 
                           tpi.favoritefolder(FavoriteCategoryID,UserCode,DbCode,DBName,RecordID,RecordTitle,FavoriteDate,FavoriteStatus)
                           values({0},'{1}','{2}','{3}','{4}','{5}',now(),1)";
            return Context.ExecuteNonQuery(string.Format(sql, favCateID, usercode, dbCode, dbName,recid,recTitle), "mysql");
        }

        /// <summary>
        /// 获取收藏列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetFavoriteFolder(int catid, string dbname, string title, DateTime? startDate, DateTime? endDate, int startIndex, int pagecount, out int count)
        {
            string userCode = SessionHelper.GetUserInfo().UserCode;
            DataTable dt = null;
            string sql = @"SELECT t.FavoriteFolderID,t.DBName,t.RecordTitle,t.RecordID as SYS_FLD_SYSID,t.DbCode,t.FavoriteDate,t.FavoriteCategoryID,fc.CategoryName,'' as DetailUrL
                            FROM tpi.favoritefolder t
                            left join tpi.favoritecategory fc on t.FavoriteCategoryID=fc.FavoriteCategoryID
                            where t.FavoriteStatus=1 and t.UserCode='" + userCode + "'";
            if (catid!=0)
            {
                sql += " and t.FavoriteCategoryID=" + catid;
            }
            if (!string.IsNullOrEmpty(dbname))
            {
                sql += " and t.DBName like '%" + dbname + "%'";
            }
            if (!string.IsNullOrEmpty(title))
            {
                sql += " and t.RecordTitle like '%" + title + "%'";
            }
            if (startDate != null && endDate != null)
            {
                sql += " and t.FavoriteDate >='" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and t.FavoriteDate <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            else if (startDate != null)
            {
                sql += " and t.FavoriteDate >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
            }
            else if (endDate != null)
            {
                sql += " and t.FavoriteDate <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
            }
            sql += " order by t.FavoriteDate desc";
            dt = Context.ExecuteSql(sql, startIndex, pagecount, out count, false, "mysql");
            return dt;

        }
        /// <summary>
        /// 更新收藏记录
        /// </summary>
        /// <param name="catename"></param>
        /// <param name="catestatus"></param>
        /// <param name="sortno"></param>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public bool UpdateFavoriteFolder(int favCateID, int floderid)
        {
            string usercode = SessionHelper.GetUserInfo().UserCode;
            string sql = @"update tpi.favoritefolder  set FavoriteCategoryID={0} where FavoriteFolderID={1}";
            return Context.ExecuteNonQuery(string.Format(sql, favCateID, floderid), "mysql");
        }
        /// <summary>
        /// 删除收藏记录
        /// </summary>
        /// <param name="catename"></param>
        /// <param name="catestatus"></param>
        /// <param name="sortno"></param>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public bool DelFavoriteFolder(int floderid)
        {
            string sql = @"update tpi.favoritefolder  set FavoriteStatus=9 where FavoriteFolderID={0}";
            return Context.ExecuteNonQuery(string.Format(sql, floderid), "mysql");
        }
        /// <summary>
        /// 批量删除收藏记录
        /// </summary>

        public bool DelFavoriteFolderBath(string floderid)
        {
            floderid = floderid.TrimEnd(',');
            string sql = @"update tpi.favoritefolder  set FavoriteStatus=9 where FavoriteFolderID in ({0})";
            return Context.ExecuteNonQuery(string.Format(sql, floderid), "mysql");
        }
        /// <summary>
        /// 取消收藏
        /// </summary>
        /// <param name="recid"></param>
        /// <returns></returns>
        public bool UpdateFavStatusByRecID(int recid)
        {
            string usercode = SessionHelper.GetUserInfo().UserCode;
            string sql = @"update tpi.favoritefolder  set FavoriteStatus=9 where RecordID='{0}' and usercode='{1}'";
            return Context.ExecuteNonQuery(string.Format(sql, recid, usercode), "mysql");
        }
        /// <summary>
        /// 根据数据库code获取该数据库的收藏记录
        /// </summary>
        /// <param name="dbcode"></param>
        /// <returns></returns>
        public DataTable GetFavFolderList(string dbcode)
        {
            string userCode = SessionHelper.GetUserInfo().UserCode;
            DataTable dt = null;
            string sql = @"SELECT * FROM tpi.favoritefolder t
                            where FavoriteStatus=1 and  t.UserCode='" + userCode + "'";
            if (!string.IsNullOrEmpty(dbcode))
            {
                sql += " and t.dbcode = '" + dbcode + "'";
            }
            dt = Context.ExecuteSql(sql,"mysql");
            return dt;

        }

        public DataTable GetSearchLogInfo(int searchLogID)
        {
            DataTable dt = null;
            try {
                string sql = @"SELECT s.Str_Query, Str_Order, classCode, dbid FROM tpi.searchlog s where s.searchlogid = {0}";
                dt = Context.ExecuteSql(string.Format(sql, searchLogID), "mysql");
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return dt;

        }
    }
}
